﻿<cfsilent>
	<cfscript>

		dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice");
		timeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");

		taskId = event.getArg("TSK");
		taskType = event.getArg("TT");

		/* 载入教学任务 */
		switch(taskType){
			case "C":
				sql = "SELECT 
							t_course.course_name, 
							t_teacher.tch_name, 
							t_term.academic_year, 
							t_term.academic_term 
						FROM 
							t_common_task a 
							INNER JOIN t_course ON t_course.cid = a.cid 
							INNER JOIN t_teacher ON t_teacher.tch_id = a.tch_id 
							INNER JOIN t_term ON t_term.term_id = a.term_id 
						WHERE 
							a.tsk_id = :taskId ";
				break;

			case "P":
				sql = "SELECT 
							t_course.course_name, 
							t_teacher.tch_name, 
							t_term.academic_year, 
							t_term.academic_term 
						FROM 
							t_task a 
							INNER JOIN t_course ON t_course.cid = a.cid 
							INNER JOIN t_teacher ON t_teacher.tch_id = a.tch_id 
							INNER JOIN t_term ON t_term.term_id = a.term_id 
						WHERE 
							a.tsk_id = :taskId ";
				break;
			
			case "R":
				sql = "SELECT 
							t_course.course_name, 
							t_teacher.tch_name, 
							t_term.academic_year, 
							t_term.academic_term 
						FROM 
							t_reopen_task a 
							INNER JOIN t_course ON t_course.cid = a.cid 
							INNER JOIN t_teacher ON t_teacher.tch_id = a.tch_id 
							INNER JOIN t_term ON t_term.term_id = a.term_id 
						WHERE 
							a.tsk_id = :taskId ";
				break;
			
			case "T":
				sql = "SELECT 
							t_course.course_name, 
							t_teacher.tch_name, 
							t_term.academic_year, 
							t_term.academic_term 
						FROM 
							t_sport_task a 
							INNER JOIN t_course ON t_course.cid = a.cid 
							INNER JOIN t_teacher ON t_teacher.tch_id = a.tch_id 
							INNER JOIN t_term ON t_term.term_id = a.term_id 
						WHERE 
							a.tsk_id = :taskId ";
				break;
		}
		queryObj = new query( datasource=application.dnsSlave );
		queryObj.addParam( name="taskId", value=taskId, cfsqltype="cf_sql_varchar" );
		rs_task = queryObj.execute( sql=sql ).getResult();
	
		/* 选课数据 */
		sql = "SELECT 
					t_student.stu_id, 
					t_student.stu_name, 
					t_class.cls_name, 
					t_class.grade, 
					t_subject.sbj_name, 
					t_institute.institute_name, 
					t_student_course.st, 
					t_student_course.choose_time, 
					t_student_course.retaking, 
					t_student_course.choose_prop 
				FROM 
					t_student_course 
					INNER JOIN t_student ON t_student.stu_id = t_student_course.stu_id 
					INNER JOIN t_class ON t_class.cls_id = t_student.cls_id 
					INNER JOIN t_subject ON t_subject.sbj_id = t_class.sbj_id 
					INNER JOIN t_institute ON t_institute.institute_id = t_subject.institute_id 
				WHERE 
					t_student_course.task_id = :taskId 
				ORDER BY 
					t_student.stu_id ";
		queryObj = new query( datasource=application.dnsSlave );
		queryObj.addParam( name="taskId", value=taskId, cfsqltype="cf_sql_varchar" );
		rs_chooser = queryObj.execute( sql=sql ).getResult();
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat(rs_task.course_name & "-" & rs_task.tch_name & "," & rs_task.academic_year & "-" & rs_task.academic_term & ",选课名单", "utf-8");

		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("选课学生", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学号,姓名,班级,年级,专业,学院,修读方式,选课时间,是否重修");

		for ( row = 1;row LTE rs_chooser.recordCount; row++ ){
			
			/* 构建选课数据 */
			rowData = rs_chooser["stu_id"][row]
					& "," & rs_chooser["stu_name"][row]
					& "," & rs_chooser["cls_name"][row]
					& "," & rs_chooser["grade"][row]
					& "," & rs_chooser["sbj_name"][row]
					& "," & rs_chooser["institute_name"][row]
					& "," & dictAdvice.getStudyType(rs_chooser["st"][row])
					& "," & timeAdvice.formatDatetimeString(rs_chooser["choose_time"][row], "MM月DD日 HH时MI分")
					& "," & dictAdvice.getBoolean(rs_chooser["retaking"][row]);
			
			/* 写入教室信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />	

</cfsilent>